package dao_service;

import i_dao.I_User_Basic_Dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.SimpleDateFormat;
import java.util.ArrayList;

import dao_pool.Pool;

import Protocol.user.BasePersonInfo;
import Protocol.user.User;

public class UserBasicImp implements I_User_Basic_Dao {
	Pool        m_Pool = Pool.getInstance();   
	
	@Override
	public boolean insertBaseInfo(BasePersonInfo baseInfo) {
		// TODO Auto-generated method stub
		
		Connection conn=m_Pool.getConnection();	
try {
	
	Statement statement = null;
	statement=conn.createStatement();
			int count=0;
			int num=0;
			String n_sql="SELECT user_id From base_info";
			ResultSet rs=statement.executeQuery(n_sql);
			while(rs.next()){
				count++;
				if(rs.getInt("user_id")!=baseInfo.getUser_id()){
					num++;
				}
				}
			if(count==num){
			int new_user_id=baseInfo.getUser_id();
			String new_user_name=baseInfo.getName();
			int new_gender=baseInfo.getGender();
			String new_signature=baseInfo.getSignature();
			String new_hobbyWords=baseInfo.getHobbyWords();
			String new_pic_path=baseInfo.getPic_path();
			String new_class=baseInfo.getClss();
			String new_grade=baseInfo.getGrade();
			String new_user_type=baseInfo.getUser_type();
			
			String sql="insert into base_info (user_id,user_name,user_gender,user_signature,user_hobby_keywords,user_photo_path,user_class,user_grade,user_type) values (?,?,?,?,?,?,?,?,?)";
			
			PreparedStatement prstmt=conn.prepareStatement(sql);
			
			prstmt.setLong(1, new_user_id);
			prstmt.setString(2,new_user_name);
			prstmt.setLong(3,new_gender);
			prstmt.setString(4,new_signature);
			prstmt.setString(5,new_hobbyWords);
			prstmt.setString(6,new_pic_path);
			prstmt.setString(7,new_class);
			prstmt.setString(8,new_grade);
			prstmt.setString(9,new_user_type);
			 prstmt.executeUpdate();
			 
		  
			return true;
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		finally {
			m_Pool.freeConnection(conn);
		}
		
		return false;
	}

	@Override
	public boolean updateBaseInfo(BasePersonInfo baseInfo) {
		// TODO Auto-generated method stub
         Connection conn=m_Pool.getConnection();	
		
		try {
			Statement statement = null;
			statement=conn.createStatement();
					int count=0;
					int num=0;
					String n_sql="SELECT user_id From base_info";
					ResultSet rs=statement.executeQuery(n_sql);
					while(rs.next()){
						count++;
						if(rs.getInt("user_id")!=baseInfo.getUser_id()){
							num++;
						}
						}
					if(count==num+1){
			int new_user_id=baseInfo.getUser_id();
			String new_user_name=baseInfo.getName();
			int new_gender=baseInfo.getGender();
			String new_signature=baseInfo.getSignature();
			String new_hobbyWords=baseInfo.getHobbyWords();
			String new_pic_path=baseInfo.getPic_path();
			String new_class=baseInfo.getClss();
			String new_grade=baseInfo.getGrade();
			String new_user_type=baseInfo.getUser_type();
			
			String sql="update base_info set user_id = ?,user_name=?,user_gender=?,user_signature=?,user_hobby_keywords=?,user_photo_path=?,user_class=?,user_grade=?,user_type=? where user_id=?";
			PreparedStatement prstmt=conn.prepareStatement(sql);
			
			prstmt.setLong(1, new_user_id);
			prstmt.setString(2,new_user_name);
			prstmt.setLong(3,new_gender);
			prstmt.setString(4,new_signature);
			prstmt.setString(5,new_hobbyWords);
			prstmt.setString(6,new_pic_path);
			prstmt.setString(7,new_class);
			prstmt.setString(8,new_grade);
			prstmt.setString(9,new_user_type);
			prstmt.setLong(10,new_user_id);
			 prstmt.executeUpdate();
			 
		 
			return true;
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		finally {
			m_Pool.freeConnection(conn);
		}
		return false;
		
	}

	@Override
	/*public BasePersonInfo getBaseInfoById(String base_info_id) {
		// TODO Auto-generated method stub
		BasePersonInfo		retBpi = null;
		Connection conn=m_Pool.getConnection();	
		Statement statement = null;
		try {
			statement=conn.createStatement();
			String sql = "select * from base_info where user_id ="+base_info_id;
			ResultSet  rs2 = statement.executeQuery(sql);
			if(rs2.next()) {
				retBpi = new BasePersonInfo();
				retBpi.setUser_id(rs2.getInt(1));
				retBpi.setName(rs2.getString(3));
				retBpi.setCategory(rs2.getInt(2));
				retBpi.setGender(rs2.getInt(4));
				
				rs2.close();
				m_Pool.freeConnection(conn);
				return retBpi;
				//a.setClientBaseInfo(clientBaseInfo);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return null;
	}*/

	public boolean deleteBaseInfo(int user_id) {
		// TODO Auto-generated method stub
		Connection conn=m_Pool.getConnection();	
		Statement statement = null;
		try {
			int count=0;
			int num=0;
			statement=conn.createStatement();
			String n_sql="SELECT user_id From base_info";
			ResultSet rs=statement.executeQuery(n_sql);
			while(rs.next()){
				count++;
				if(rs.getInt("user_id")!=user_id){
					num++;
				}
				
			}
			if(count==num+1){
			statement=conn.createStatement();
			String sql="DELETE from base_info where user_id='"+user_id+"'";
			statement.execute(sql);
			return true;
			}
			m_Pool.freeConnection(conn);
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return false;
	
	}

	@Override
	public BasePersonInfo getBaseInfoById(int user_id) {
		// TODO Auto-generated method stub
		BasePersonInfo bpi=null;
		Connection conn=m_Pool.getConnection();	
		Statement statement = null;
		try {
			statement=conn.createStatement();
			String sql="select*from base_info where user_id='"+user_id+"'";
			ResultSet rs = statement.executeQuery(sql);
			
			if(rs.next()) {
				bpi = new BasePersonInfo();
				bpi.setUser_id(rs.getInt("user_id"));
				bpi.setName(rs.getString("user_name"));
				bpi.setGender(rs.getInt("user_gender"));
				bpi.setSignature(rs.getString("user_signature"));
				bpi.setHobbyWords(rs.getString("user_hobby_keywords"));
				bpi.setPic_path(rs.getString("user_photo_path"));
				bpi.setClss(rs.getString("user_class"));
				bpi.setGrade(rs.getString("user_grade"));
				bpi.setUser_type(rs.getString("user_type"));
				rs.close();
				m_Pool.freeConnection(conn);
				return bpi;
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return null;
	}

	@Override
	public ArrayList<Integer> getSimilarHobbyUserId(String hobby_keywords) {
		// TODO Auto-generated method stub
		ArrayList<Integer> arr=new ArrayList<Integer>();
		Connection conn=m_Pool.getConnection();	
		Statement statement = null;
		try {
			statement=conn.createStatement();
			String sql="select user_id from base_info where user_hobby_keywords like'%"+hobby_keywords+"%'";
			
			ResultSet rs = statement.executeQuery(sql);
			while(rs.next())
			arr.add(rs.getInt("user_id"));
			m_Pool.freeConnection(conn);
			  return arr;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return null;
		
	}

	@Override
	public boolean deleteBaseInfos(ArrayList<Integer> userIdList) {
		// TODO Auto-generated method stub
		Connection conn=m_Pool.getConnection();	
		Statement statement = null;
		try {
			for(int i=0;i<userIdList.size();i++){
				int count=0;
				int num=0;
				statement=conn.createStatement();
				String n_sql="SELECT user_id From base_info";
				ResultSet rs=statement.executeQuery(n_sql);
				while(rs.next()){
					count++;
					if(rs.getInt("user_id")!=userIdList.get(i)){
						num++;
					}
					
				}
				if(count==num+1){
				statement=conn.createStatement();
				String sql="DELETE from base_info where user_id='"+userIdList.get(i)+"'";
				statement.execute(sql);
				return true;
				}
			}
			m_Pool.freeConnection(conn);
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return false;
		
	}

	@Override
	public boolean insertBaseInfos(ArrayList<BasePersonInfo> bpiList) {
		// TODO Auto-generated method stub
		Connection conn=m_Pool.getConnection();	
		try {
			
			
					for(int i=0;i<bpiList.size();i++){
						
						Statement statement = null;
						statement=conn.createStatement();
								int count=0;
								int num=0;
								String n_sql="SELECT user_id From base_info";
								ResultSet rs=statement.executeQuery(n_sql);
								while(rs.next()){
									count++;
									if(rs.getInt("user_id")!=bpiList.get(i).getUser_id()){
										num++;
									}
									}
								if(count==num){
					int new_user_id=bpiList.get(i).getUser_id();
					String new_user_name=bpiList.get(i).getName();
					int new_gender=bpiList.get(i).getGender();
					String new_signature=bpiList.get(i).getSignature();
					String new_hobbyWords=bpiList.get(i).getHobbyWords();
					String new_pic_path=bpiList.get(i).getPic_path();
					String new_class=bpiList.get(i).getClss();
					String new_grade=bpiList.get(i).getGrade();
					String new_user_type=bpiList.get(i).getUser_type();
					
					String sql="insert into base_info (user_id,user_name,user_gender,user_signature,user_hobby_keywords,user_photo_path,user_class,user_grade,user_type) values (?,?,?,?,?,?,?,?,?)";
					
					PreparedStatement prstmt=conn.prepareStatement(sql);
					
					prstmt.setLong(1, new_user_id);
					prstmt.setString(2,new_user_name);
					prstmt.setLong(3,new_gender);
					prstmt.setString(4,new_signature);
					prstmt.setString(5,new_hobbyWords);
					prstmt.setString(6,new_pic_path);
					prstmt.setString(7,new_class);
					prstmt.setString(8,new_grade);
					prstmt.setString(9,new_user_type);
					 prstmt.executeUpdate();
					}
				  
					return true;
					}
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
				finally {
					m_Pool.freeConnection(conn);
				}
		return false;
	}

}
